package reservation;

import java.sql.*;

public class SearchReservation {
	private String message;
	private String roomId;
	private String dateOne;
	private String dateTwo;
	private int capacity;
	private String myQuery;
	
	public String execute(){
		//if(roomId != null){//is there a better way to implement this searching other than tons of nested if elses?
				
		//}else if(dateOne != null || dateTwo != null){
			
		
		//}else if(capacity > 0){
		
	//	}
		//else{//show all rooms	
		if(dateOne != null && dateTwo != null)
		{
			try{

				//myQuery = "SET @date1 = '"+dateOne+"';";
			//	myQuery += "SET @date2 = '"+dateTwo+"';";
				myQuery = "SELECT ro.RoomNumber, re.ReservationStart FROM ROOM ro, RESERVATION re ";
				myQuery += "WHERE ro.RoomID = re.RoomID ";
				myQuery += "AND STR_TO_DATE('"+dateOne+"','%d/%m/%Y') < re.ReservationStart ";
				myQuery += "AND re.ReservationStart < STR_TO_DATE('"+dateTwo+"','%d/%m/%Y');";
				
				runQuery(myQuery);
			}catch(Exception e){
				message= e.getMessage();
			}
		}
		
	 return "Success";
	}
	
	public String runQuery(String query)throws SQLException{
		Connection connection = null;
		message = "";
		
		try{
			DriverManager.registerDriver(new com.mysql.jdbc.Driver());
			connection = DriverManager.getConnection("jdbc:mysql://137.190.19.20:3306/reservations","reservations","Hello912");
			
			Statement sqlStatement = connection.createStatement();
			ResultSet sqlResult = sqlStatement.executeQuery(query);
			
			while(sqlResult.next())
			{
				message += sqlResult.getString(1) + " <br/> " +sqlResult.getString(2);//+"\t\t"//+sqlResult.getString(2)+"\t\t"+sqlResult.getString(3)+"\t\t"+sqlResult.getString(4)
						//+"\t\t"+sqlResult.getString(5)+"\t\t"+sqlResult.getString(6);
			}
			sqlResult.close();
			sqlStatement.close();
						
		}catch(Exception e){
			System.out.println("Exception : " + e);
			
		}finally{
			connection.close();
		}
//		SELECT *
//		FROM ROOM ro, RESERVATION re
//		WHERE ro.RoomID = re.RoomID
//		AND NOW() < re.ReservationStart
//
//		--Select all reservations between date1 and date2
//
//		SET @date1 = '2013-04-08 00:00:00';
//		SET @date2 = '2013-04-29 00:00:00';
//		SELECT *
//		FROM ROOM ro, RESERVATION re
//		WHERE ro.RoomID = re.RoomID
//		AND @date1 < re.ReservationStart
//		AND re.ReservationStart < @date2
//
//		--Select all rooms not reserved between date1 and date2
//
//		SET @date1 = '2013-04-08 00:00:00';
//		SET @date2 = '2013-04-29 00:00:00';
//
//		SELECT * FROM ROOM re1 WHERE re1.RoomID NOT IN (
//		SELECT re.RoomID
//		FROM ROOM ro, RESERVATION re
//		WHERE ro.RoomID = re.RoomID
//		AND @date1 < re.ReservationStart
//		AND re.ReservationStart < @date2
//		)
//
//		--Select all reservations between date1 and date2 for a room by RoomID
//
//		SET @date1 = '2013-04-08 00:00:00';
//		SET @date2 = '2013-04-29 00:00:00';
//		SET @roomID ='1';
//		SELECT *
//		FROM RESERVATION re
//		WHERE re.RoomID = @roomID
//		AND @date1 < re.ReservationStart
//		AND re.ReservationStart < @date2
//
//		--Select all reservations between date1 and date2 for a room by RoomNumber
//
//		SET @date1 = '2013-04-08 00:00:00';
//		SET @date2 = '2013-04-29 00:00:00';
//		SET @roomNumber ='105';
//		SELECT *
//		FROM RESERVATION re 
//		WHERE re.RoomID IN (
//		SELECT re.RoomID
//		FROM ROOM ro, RESERVATION re
//		WHERE re.RoomID = ro.RoomID
//		AND ro.RoomNumber = @roomNumber
//		AND @date1 < re.ReservationStart
//		AND re.ReservationStart < @date2
//		)
		return message;
	}
	
//	public String checkReservation(){
//		message="checking Reservations";
//		 return "Success";
//	}
//	
//	public String cancelReservation(){
//		message="Cancelling Reservations";
//		 return "Success";
//	}
	
	public String showAllRooms()throws SQLException{
		Connection connection = null;
		
		try{
			DriverManager.registerDriver(new com.mysql.jdbc.Driver());
			connection = DriverManager.getConnection("jdbc:mysql://137.190.19.20:3306/reservations","reservations","Hello912");
			
			Statement sqlStatement = connection.createStatement();
			ResultSet sqlResult = sqlStatement.executeQuery("SELECT bg.BuildingName, ro.RoomNumber, ro.RoomCapacity," +
					" ro.RoomDescription, ro.RoomPictureLink  from ROOM ro, BUILDING bg" +
					"WHERE ro.BuildingID = bg.BuildingID");
			
			message += "\nBuilding \t\t| Room Number \t\t| Capacity \t\t| Description \t\t| Picture \n";

			while(sqlResult.next())
			{
				message += sqlResult.getString(1)+"\t\t"+sqlResult.getString(2)+"\t\t"+sqlResult.getString(3)+"\t\t"+sqlResult.getString(4)
						+"\t\t"+sqlResult.getString(5);
			}
			sqlResult.close();
			sqlStatement.close();
						
		}catch(Exception e){
			System.out.println("Exception : " + e);
			
		}finally{
			connection.close();
		}
		return "Success";
	}
		
    public String getMessage() {
        return message;
    }
  
    public void setMessage(String message) {
        this.message = message;
    }
    
    public String getRoomId() {
        return roomId;
    }
  
    public void setRoomId(String roomId) {
        this.roomId = roomId;
    }
    
    public String getDateOne() {
        return dateOne;
    }
  
    public void setDateOne(String dateOne) {
        this.dateOne = dateOne;
    }
    
    public String getDateTwo() {
        return dateTwo;
    }
  
    public void setDateTwo(String dateTwo) {
        this.dateTwo = dateTwo;
    }
    
    public int getCapacity() {
        return capacity;
    }
  
    public void setCapacity(int capacity) {
        this.capacity = capacity;
    }
    
    public int getBuilding() {
        return building;
    }
  
    public void setBuilding(int building) {
        this.building = building;
    }
    
    public String getMyQuery(){
        return myQuery;
    }
    
    public void setMyQuery(String query){
    	myQuery = query;
    }
    
    
    
}

